﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Ninject;
using Dapper;

namespace VideoChat.Models
{
    public class DataBaseUsersStore : IUsersStore
    {
        private static readonly string ConnectionString =
              ConfigurationManager.ConnectionStrings["DatabaseConnection"].ConnectionString;

        //-------------------------------------------------------------------------------------------------------

        public static IDbConnection GetConnection()
        {
            var connection = new SqlConnection(ConnectionString);
            connection.Open();
            return connection;
        }

        public bool Exists(string key)
        {
            using (var db = GetConnection())
            {
                return db.Query<int>("select 1 from Account where UserKey = @key", new { key }).FirstOrDefault() > 0;
            }
        }

        public void Add(IUser user)
        {
            using (var db = GetConnection())
            {
                db.Execute("if not exists(select 1 from Account where UserKey = @key) " +
                           "insert into Account(UserKey, UserName) values (@key, @name);" +
                           "else " +
                           "update Account set UserName = @name, deleted = null where UserKey = @key;",
                    new
                    {
                        name = user.Name,
                        key = user.Key
                    });
            }
        }

        public void Remove(string key)
        {
            using (var db = GetConnection())
            {
                db.Execute("update Account set deleted = 1 where UserKey = @key",
                    new
                    {
                        key
                    });
            }
        }

        public IList<IUser> GetList()
        {
            using (var db = GetConnection())
            {
                return db.Query<User>("select UserKey as [Key], UserName as Name from Account " +
                                      "where deleted is null or Deleted <> 1").ToList().Cast<IUser>().ToList();
            }
        }

        IDictionary<string, DateTime> _floodList = new Dictionary<string, DateTime>();
        public bool CheckFlooder(string key)
        {
            if (!_floodList.ContainsKey(key) || _floodList[key] < DateTime.Now.AddMinutes(-10))
                using (var db = GetConnection())
                {
                    int result = db.Query<int>("select count(1) from Chat where SenderKey = @key and SendDate > @date",
                        new
                            {
                                @key,
                                date = DateTime.Now.AddSeconds(-30)
                            }
                        ).FirstOrDefault();
                    if (result > 15 && !_floodList.ContainsKey(key))
                        _floodList.Add(key, DateTime.Now);
                    else if (result < 15 && _floodList.ContainsKey(key))
                        _floodList.Remove(key);
                    return result > 15;
                }
            else
                return true;
        }
        public void SaveMessage(ChatMessage message)
        {
            using (var db = GetConnection())
            {
                db.Execute(
                    "insert into Chat(RoomKey, SenderKey, SenderName, Content, SendDate) values" +
                    "(@RoomKey, @SenderKey, @SenderName, @Content, @SendDate);",
                    new
                    {
                        message.RoomKey,
                        message.SenderKey,
                        message.SenderName,
                        message.Content,
                        SendDate = message.Date
                    });
            }
        }

        public void SaveRoomInfo(RoomModel model)
        {
            using (var db = GetConnection())
            {
                db.Execute(
                    "insert into RoomInfo(RoomKey, MyPublicKey, MyKey, MyName, CalleePublicKey, CalleeKey, CalleeName, StartDate) " +
                    "values (@RoomKey, @MyPublicKey, @MyKey, @MyName, @CalleePublicKey, @CalleeKey, @CalleeName, @StartDate)",
                    new
                    {
                        model.RoomKey,
                        model.MyPublicKey,
                        model.MyKey,
                        model.MyName,
                        model.CalleePublicKey,
                        model.CalleeKey,
                        model.CalleeName,
                        StartDate = DateTime.Now
                    });
            }
        }
        public RoomModel GetRoomInfo(string key)
        {
            using (var db = GetConnection())
            {
                return db.Query<RoomModel>(
                    "select * from RoomInfo where RoomKey = @key",
                    new { key }).FirstOrDefault();
            }
        }
        public void CloseRoom(string chatKey)
        {
            using (var db = GetConnection())
            {
                db.Execute(
                    "update RoomInfo set EndDate = @endDate where RoomKey = @chatKey",
                    new
                    {
                        chatKey,
                        endDate = DateTime.Now
                    });
            }
        }


        public IList<ChatMessage> GetChatHistory(string roomKey)
        {
            using (var db = GetConnection())
            {
                return db.Query<ChatMessage>("select top 50 *, SendDate as Date from Chat where RoomKey = @roomKey " +
                                             "order by SendDate desc", new { roomKey }).ToList();
            }
        }

        public void CloseRoom(string roomKey, string key, VideoState state)
        {
            using (var db = GetConnection())
            {
                db.Execute("if exists(select 1 from RoomInfo where RoomKey = @roomKey and MyPublicKey = @key) " +
                           "update RoomInfo set MyVideoState = @videoState where RoomKey = @roomKey and MyPublicKey = @key " +
                           "else if exists(select 1 from RoomInfo where RoomKey = @roomKey and CalleePublicKey = @key) " +
                           "update RoomInfo set CalleeVideoState = @videoState where RoomKey = @roomKey and CalleePublicKey = @key",
                           new { roomKey, key, videoState = (int)state });
            }
        }

        public void SaveRoomState(string roomKey, string key, bool isCam, bool isCamOn, bool isMic, bool isMicOn)
        {
            using (var db = GetConnection())
            {
                var result = db.Execute("if exists(select 1 from RoomInfo where RoomKey = @roomKey and MyKey = @key) " +
                           "update RoomInfo set MyCam = @isCam, MyCamOn = @isCamOn, MyMic = @isMic, MyMicOn = @isMicOn " +
                           "where RoomKey = @roomKey and MyKey = @key else " +
                           "if exists(select 1 from RoomInfo where RoomKey = @roomKey and CalleeKey = @key) " +
                           "update RoomInfo set CalleeCam = @isCam, CalleeCamOn = @isCamOn, CalleeMic = @isMic, CalleeMicOn = @isMicOn " +
                           "where RoomKey = @roomKey and CalleeKey = @key",
                           new
                           {
                               roomKey,
                               key,
                               isCam = isCam ? 1 : 0,
                               isCamOn = isCamOn ? 1 : 0,
                               isMic = isMic ? 1 : 0,
                               isMicOn = isMicOn ? 1 : 0
                           }) > 0;
            }
        }
    }
}